package com.easyjava.builder;

import com.easyjava.bean.Constants;
import com.easyjava.bean.FieldInfo;
import com.easyjava.bean.TableInfo;
import com.easyjava.utils.JsonUtils;
import com.easyjava.utils.PropertiesUitls;
import com.easyjava.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * @ClassName BuildTable
 * @Date 2024/5/29  14:44
 **/
public class BuildTable {
    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private static Connection conn = null;

    private static String SQL_SHOW_TABLE_STATUS = "show table status";

    private static String SQL_SHOW_TABLE_INDEX = "show index from %s";

    private static String SQL_SHOW_TABLE_FIELDS = "show full fields from %s";

    static {
        String driverName = PropertiesUitls.getString("db.driver.name");
        String url = PropertiesUitls.getString("db.url");
        String user = PropertiesUitls.getString("db.username");
        String password = PropertiesUitls.getString("db.password");
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            logger.error("数据库连接失败", e);
        }
    }

    public static List<TableInfo> getTables() {
        PreparedStatement ps = null;
        ResultSet tableResult = null;
        List<TableInfo> tableInfoList = new ArrayList<>();


        try {
            ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tableResult = ps.executeQuery();
            while (tableResult.next()) {

                String tableName = tableResult.getString("name");
                String comment = tableResult.getString("comment");
                logger.info("tableName:{},comment:{}", tableName, comment);


                String beanName = tableName;

                if (Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(beanName.indexOf("_") + 1);
                }
                beanName = processField(beanName,true);
                logger.info(beanName);
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName+Constants.SUFFIX_BEAN_QUERY);
                readFieldInfo(tableInfo);

                getKeyIndexInfo(tableInfo);
                tableInfoList.add(tableInfo);
//                logger.info("tableInfo:{}", JsonUtils.convertObjToJson(tableInfo));
            }
        } catch (Exception e) {
            logger.error("读取表失败", e);
        } finally {
            if (tableResult != null) {
                try {
                    tableResult.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return tableInfoList;
    }


    private static void readFieldInfo(TableInfo tableInfo){
        PreparedStatement ps = null;
        ResultSet fieldResult = null;
        List<FieldInfo> fieldInfoList = new ArrayList<>();

        List<FieldInfo> fieldExtendList = new ArrayList<>();

        try {
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS,tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            Boolean haveDateTime = false;
            Boolean haveDate = false;
            Boolean haveBigDecimal = false;
            while (fieldResult.next()) {

                String field = fieldResult.getString("field");
                String type = fieldResult.getString("type");
                String extra = fieldResult.getString("extra");
                String comment = fieldResult.getString("comment");
                if(type.indexOf("(")>0){
                    type = type.substring(0,type.indexOf("("));
                }
                String propertyName = processField(field,false);

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfoList.add(fieldInfo);

                fieldInfo.setFieldName(field);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra));
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));
//                logger.info("field:{},propertyName:{},type:{},extra:{},comment:{}",field,propertyName,type,extra,comment);
//                logger.info("javaType:{}",fieldInfo.getJavaType());
                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE,type)){
                    haveDateTime = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DATE_TYPE,type)){
                    haveDateTime = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)){
                    haveBigDecimal = true;
                }

                if (ArrayUtils.contains(Constants.SQL_STRING_TYPE, type)) {
                    String propertyNameFuzzy = fieldInfo.getPropertyName() + Constants.SUFFIX_QUERY_FUZZY;
                    FieldInfo fuzzyField = new FieldInfo();
                    fuzzyField.setJavaType(fieldInfo.getJavaType());
                    fuzzyField.setPropertyName(propertyNameFuzzy);
                    fuzzyField.setFieldName(fieldInfo.getFieldName());
                    fuzzyField.setSqlType(type);
                    fieldExtendList.add(fuzzyField);
                }

                if (ArrayUtils.contains(Constants.SQL_DATE_TYPE, type) || ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE,type)) {
                    String propertyNameStart = fieldInfo.getPropertyName() + Constants.SUFFIX_QUERY_TIME_START;
                    FieldInfo fuzzyTimeStart = new FieldInfo();
                    fuzzyTimeStart.setJavaType("String");
                    fuzzyTimeStart.setPropertyName(propertyNameStart);
                    fuzzyTimeStart.setFieldName(fieldInfo.getFieldName());
                    fuzzyTimeStart.setSqlType(type);
                    fieldExtendList.add(fuzzyTimeStart);

                    String propertyNameEnd = fieldInfo.getPropertyName() + Constants.SUFFIX_QUERY_TIME_END;
                    FieldInfo fuzzyTimeEnd = new FieldInfo();
                    fuzzyTimeEnd.setJavaType("String");
                    fuzzyTimeEnd.setPropertyName(propertyNameEnd);
                    fuzzyTimeEnd.setFieldName(fieldInfo.getFieldName());
                    fuzzyTimeEnd.setSqlType(type);
                    fieldExtendList.add(fuzzyTimeEnd);
                }

            }
            tableInfo.setHaveDateTime(haveDateTime);
            tableInfo.setHaveDate(haveDate);
            tableInfo.setHavaBigDecimal(haveBigDecimal);
            tableInfo.setFieldList(fieldInfoList);
            tableInfo.setFieldExtendList(fieldExtendList);
        } catch (Exception e) {
            logger.error("读取表失败", e);
        } finally {
            if (fieldResult != null) {
                try {
                    fieldResult.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }



    private static List<FieldInfo> getKeyIndexInfo(TableInfo tableInfo){
        PreparedStatement ps = null;
        ResultSet fieldResult = null;
        List<FieldInfo> fieldInfoList = new ArrayList<>();

        try {

            Map<String,FieldInfo> tempMap = new HashMap<>();
            for (FieldInfo fieldInfo:tableInfo.getFieldList()) {
                 tempMap.put(fieldInfo.getFieldName(),fieldInfo);

            }

            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX,tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            while (fieldResult.next()) {

                String keyName = fieldResult.getString("key_name");
                Integer noneUnique = fieldResult.getInt("non_unique");
                String columnName = fieldResult.getString("column_name");
                if(noneUnique==1){
                    continue;
                }
                List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if(keyFieldList==null){
                    keyFieldList = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName,keyFieldList);
                }
                keyFieldList.add(tempMap.get(columnName));

            }
        } catch (Exception e) {
            logger.error("读取索引失败", e);
        } finally {
            if (fieldResult != null) {
                try {
                    fieldResult.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return fieldInfoList;
    }

    private static String processField(String field, Boolean uperCaseFirstLetter){
        StringBuffer stringBuffer = new StringBuffer();
        String[] fields = field.split("_");
        stringBuffer.append(uperCaseFirstLetter? StringUtils.uperCaseFirstLetter(fields[0]) :fields[0]);
        for (int i = 1, length = fields.length;i <length ; i++) {
            stringBuffer.append(StringUtils.uperCaseFirstLetter(fields[i]));
        }
        return stringBuffer.toString();
    }

    private static String processJavaType(String type){
        if(ArrayUtils.contains(Constants.SQL_INTEGER_TYPE,type)){
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPE,type)) {
            return "Long";
        }else if(ArrayUtils.contains(Constants.SQL_STRING_TYPE,type)){
            return "String";
        }else if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE,type)||ArrayUtils.contains(Constants.SQL_DATE_TYPE,type)){
            return "Date";
        }else if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)){
            return "BigDecimal";
        }else {
            throw new RuntimeException("无法识别的类型："+type);
        }
    }
}

